﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Includes;
using MySql.Data;
using MySql.Data.MySqlClient;


namespace AsyncroneModule
{
    /// <summary>
    /// Mysql database implementation of the persistancy layer interface
    /// </summary>
    public class PersistAsync : IPersistAsync
    {
        private MySqlConnection connection;

        private const String myConnectionString = @"SERVER=localhost;
                                                  DATABASE=homecaresystems;
                                                  UID=hcs;
                                                  PASSWORD=hcs_starting1st;";



        /// <summary>
        /// Checks whether a patient with the given number exists
        /// </summary>
        /// <param name="tel"> Telephone number to check the system for </param>
        /// <returns> true if patient exists, false otherwise </returns>
        public bool isPatient(String tel)
        {
           bool patientExists = false;

           connect();

           // Get all entries of patients that are active with the provided number
           MySqlCommand command = connection.CreateCommand();
           command.CommandText = @"SELECT m.ID
                                   FROM Mapping m, Patientendaten p
                                   WHERE
                                   m.TEL = '" + tel + @"' AND 
                                   m.ID = p.ID AND
                                   p.Status = 1;";

           // See whether there is acutually an entry
           MySqlDataReader reader = command.ExecuteReader();
           if(reader.HasRows)
               patientExists = true;
           else
               patientExists = false;
           
           disconnect();

           return patientExists;
        }

        /// <summary>
        /// Checks whether the submitted value is a resubmit, or a new submit
        /// </summary>
        /// <param name="tel"> Telephone number to check the system for </param>
        /// <param name="timeDif"> Time difference for it to be considered a resubmit </param>
        /// <returns> true if patient already submitted vitaldata during the last timeDif seconds, false otherwise </returns>
        public bool isResubmit(String tel, int timeDif)
        {
           bool resubmit = false;

           connect();

           // Get all entries of the submitted vitaldata for the patient with this number
           MySqlCommand command = connection.CreateCommand();
           command.CommandText = @"SELECT m.ID
                                   FROM Mapping m, Vitaldaten v
                                   WHERE
                                   m.TEL = '" + tel + @"' AND 
                                   m.ID = v.ID AND
                                   ((UNIX_TIMESTAMP(v.Timestamp)+ " + timeDif + @") > UNIX_TIMESTAMP(NOW())) 
                                   p.Status = 1;";

           // See whether there is acutually an entry
           MySqlDataReader reader = command.ExecuteReader();
           if (reader.HasRows)
              resubmit = true;
           else
              resubmit = false;

           disconnect();

           return resubmit;
        }

        /// <summary>
        /// Get Flags for specified patient
        /// </summary>
        /// <param name="tel"> Patients number </param>
        /// <returns> Flagset for the patient </returns>
        public Flags getFlags(String tel)
        {
           Flags flags = new Flags();

           connect();

           // Get the flags
           MySqlCommand command = connection.CreateCommand();
           command.CommandText = @"SELECT s.Informiert, s.SuccessiveD, s.KR
                                 FROM Mapping m, Steuerdaten s, Patientendaten p
                                 WHERE 
                                 m.ID = s.ID AND
                                 m.ID = p.ID AND
                                 m.TEL = '" + tel + @"' AND 
                                 p.Status = 1;";

           // Copy to result
           MySqlDataReader Reader = command.ExecuteReader();
           Reader.Read();
           try
           {
              flags.info = Reader.GetBoolean("Informiert");
              flags.rd = Reader.GetBoolean("SuccessiveD");
              flags.kr = Reader.GetBoolean("KR");
           }
           catch (Exception)
           {
              addLog("000000000000000", "Error while fetching flags with tel: " + tel);
           }

           disconnect();

           return flags;
        }


        /// <summary>
        /// Gets the VitalData of the patient with the telephonenumber
        /// </summary>
        /// <param name="tel"> The telephonenumber of the patient </param>
        /// <returns> VitalData which belong to the telephonenumber </returns>
        public VitalData getVData(String tel) {

            VitalData res = new VitalData();
        
            connect();

            // Get vitaldata from DB
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = @"SELECT m.ID, 
                                    p.NormBlutdruck, p.ZABlutdruck, p.KABlutdruck,
                                    p.NormPuls, p.ZAPuls, p.KAPuls,
                                    p.NormGewicht, p.ZAGewicht, p.KAGewicht
                                    FROM Mapping m, Patientendaten p
                                    WHERE 
                                    m.TEL = '" + tel + @"' AND 
                                    m.ID = p.ID AND 
                                    p.Status = 1;";
            
            MySqlDataReader reader = command.ExecuteReader();
            
            // Copy to result
            try
            {
                reader.Read();

                res.normalBlood  = reader.GetInt32("NormBlutdruck");
                res.derBlood     = ((float)reader.GetInt32("ZABlutdruck")/100f);
                res.critDerBlood = ((float)reader.GetInt32("KABlutdruck")/100f);

                res.normalPulse  = reader.GetInt32("NormPuls");
                res.derPulse     = ((float)reader.GetInt32("ZAPuls")/100f);
                res.critDerPulse = ((float)reader.GetInt32("KAPuls")/100f);

                res.normalWeight  = reader.GetInt32("NormGewicht");
                res.derWeight     = ((float)reader.GetInt32("ZAGewicht")/100f);
                res.critDerWeight = ((float)reader.GetInt32("KAGewicht")/100f);
            }
            catch (Exception)
            {
                addLog("000000000000000", "Error while fetching vdata with tel: " + tel + "Maybe patient does not exist?");
            }

            return res;
        }


        /// <summary>
        /// Gets the Name of the Patient
        /// </summary>
        /// <param name="tel"> The telephonenumber of the patient </param>
        /// <returns> The Name as String </returns>
        public String getPatientName(String tel) {

            String name = "";

            connect();

            // Get name
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = @"SELECT p.Vorname, p.Nachname 
                                    FROM Mapping m, Patientendaten p
                                    WHERE 
                                    m.TEL = '" + tel + @"' AND 
                                    m.ID = p.ID AND 
                                    p.Status = 1;";

            MySqlDataReader reader = command.ExecuteReader();
            
            // Combine to result
            try
            {
                reader.Read();
               
                name = reader.GetString("Vorname") + " " + reader.GetString("Nachname");
            }
            catch (Exception)
            {
                addLog("000000000000000", "Error while fetching name with tel: " + tel);
            }

            disconnect();

            return name;
        }


        /// <summary>
        /// Adds a log to the log table
        /// </summary>
        /// <param name="tel"> Number to log for </param>
        /// <param name="log"> Text to log </param>
        public void addLog(String tel, String log)
        {
           connect();

           // Translate Tel to ID
           MySqlCommand command = connection.CreateCommand();
           command.CommandText = @"SELECT m.ID
                                   FROM  Mapping m, Patientendaten p 
                                   WHERE 
                                   m.TEL = '" + tel + @"' AND   
                                   m.ID  = p.ID AND   
                                   p.Status = 1;";

           int id;

           try 
           { 
               id = int.Parse(command.ExecuteScalar().ToString()); 
           }
           catch (Exception) 
           { 
               id = 0; 
           }

           // Insert log
           command.CommandText = @"INSERT INTO Log (ID, Timestamp, Eintrag)
                                  VALUES (" + id + ", CURRENT_TIMESTAMP, '" + log + "');";

           command.ExecuteNonQuery();

           disconnect();

           return;
        }


        /// <summary>
        /// Set flags for the specified patient
        /// </summary>
        /// <param name="tel"> Number of patient </param>
        /// <param name="flags"> Flags to set </param>
        public void setFlags(String tel, Flags flags)
        {
           connect();

           // Set flags
           MySqlCommand command = connection.CreateCommand();
           command.CommandText = @"UPDATE Steuerdaten s, Mapping m, Patientendaten p
                                SET Informiert = " + (flags.info ? 1 : 0) + @",
                                    KR = " + (flags.kr ? 1 : 0) + @",
                                    SuccessiveD = " + (flags.rd ? 1 : 0) +
                                    (((flags.kr == true) && (getFlags(tel).kr == false)) ? ", KR_Timestamp = NOW()" : "") +
                                  @" WHERE 
                                  m.TEL = '" + tel + @"' AND 
                                  m.ID  = s.ID AND 
                                  m.ID  = p.ID AND
                                  p.status = 1";

           command.ExecuteReader();

           disconnect();
        }


        /// <summary>
        /// Adds the Vitals to the list of Vitals from the patient found through the telephonenumber
        /// </summary>
        /// <param name="tel"> The telephonenumber of the patient </param>
        /// <param name="vitals"> The Vitals which are to be added to the list in the Persistence </param>
        public void addVital(String tel, Vitals vitals) {

            connect();

            // Translate Tel to ID
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = @"SELECT m.ID 
                                    FROM Mapping m, Patientendaten p 
                                    WHERE m.TEL = '" + tel + @"' AND
                                    m.ID = p.ID AND
                                    p.Status = 1;";

            int id;

            try
            {
               id = int.Parse(command.ExecuteScalar().ToString());
            }
            catch (Exception)
            {
               id = 0;
            }

            // Insert Vitaldata
            command.CommandText = @"INSERT INTO vitaldaten (ID, Timestamp, Bloodpressure, Puls, Weight) 
                                    VALUES (" + id + ", CURRENT_TIMESTAMP, '" + vitals.blood + "', '" + vitals.pulse + "', '" + vitals.weight + "');";

            command.ExecuteNonQuery();

            // TODO ?
            // Delete vital data the user has submitted less than 15 minutes ago (to prevent multible submits in one check period)
//            command.CommandText = @"DELETE FROM vitaldaten WHERE ID = " + id + " AND (UNIX_TIMESTAMP(Timestamp)+15*60) > UNIX_TIMESTAMP(NOW());";
//
//            command.ExecuteNonQuery();


            disconnect();

            return;
        }



        private void connect()
        {
           connection = new MySqlConnection(myConnectionString);
           try
           {
              connection.Open();
           }
           catch (MySqlException e)
           {
              throw e;
           }
        }

        private void disconnect()
        {
            connection.Close();
        }
    }
}